﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using HellCook.Model;
using System.Data.SqlClient;

namespace HellCook.DataAccess
{
    public static class Ingrediente_AdministradorBaseDeDatos
    {
        private static string PathDataBase = "Data Source=.\\SQLEXPRESS; Initial Catalog=HellCook_DataBase; Integrated Security=true";


        public static Collection<Ingrediente> ObtenerIngredienteCercanosALaFechaDeVencimiento()
        {
            Collection<Ingrediente> List_Ingredients = new Collection<Ingrediente>();
            SqlDataReader ReaderInfo = null;

            SqlConnection SQLConnection;
            SQLConnection = new SqlConnection(PathDataBase);

            SqlCommand SQLCommand = SQLConnection.CreateCommand();
            SQLCommand.CommandText = "SELECT * FROM [Ingredient] WHERE Fecha_Expiracion = @Fecha OR Fecha_Expiracion <= @FechaPasada";
            
            SQLCommand.Parameters.AddWithValue("Fecha", DateTime.Today.AddDays(1));
            SQLCommand.Parameters.AddWithValue("FechaPasada", DateTime.Today);            

            try
            {
                SQLConnection.Open();
                ReaderInfo = SQLCommand.ExecuteReader();

                while (ReaderInfo.Read())
                {
                    Ingrediente Item = new Ingrediente();

                    Item.Codigo = Convert.ToInt16(ReaderInfo["Codigo"].ToString());
                    Item.Nombre_Ingrediente = ReaderInfo["Nombre_Ingrediente"].ToString();
                    Item.Cantidad = Convert.ToInt16(ReaderInfo["Cantidad"].ToString());
                    Item.Tipo_Medida = ReaderInfo["Tipo_Medida"].ToString();
                    Item.Fecha_Expiracion = Convert.ToDateTime(ReaderInfo["Fecha_Expiracion"].ToString());
                    Item.Grupo_Alimenticio = ReaderInfo["Grupo_Alimenticio"].ToString();
                    //Item.ImageIngredient = ReaderInfo["Image"];
                    Item.Cantidad_Maxima = Convert.ToInt16(ReaderInfo["Cantidad_Maxima"].ToString());

                    List_Ingredients.Add(Item);
                }
            }
            catch (Exception)
            {
                List_Ingredients = null;
            }
            finally
            {
                if (SQLConnection.State == System.Data.ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }
            return (List_Ingredients);
        }


        public static Collection<Ingrediente> ObtenerIngredienteConCantidadMaxima()
        {
            Collection<Ingrediente> List_Ingredients = new Collection<Ingrediente>();
            SqlDataReader ReaderInfo = null;

            SqlConnection SQLConnection;
            SQLConnection = new SqlConnection(PathDataBase);

            SqlCommand SQLCommand = SQLConnection.CreateCommand();
            SQLCommand.CommandText = "SELECT * FROM [Ingredient] WHERE Cantidad_Maxima > 0";

            try
            {
                SQLConnection.Open();
                ReaderInfo = SQLCommand.ExecuteReader();

                while (ReaderInfo.Read())
                {
                    Ingrediente Item = new Ingrediente();

                    Item.Codigo = Convert.ToInt16(ReaderInfo["Codigo"].ToString());
                    Item.Nombre_Ingrediente = ReaderInfo["Nombre_Ingrediente"].ToString();
                    Item.Cantidad = Convert.ToInt16(ReaderInfo["Cantidad"].ToString());
                    Item.Tipo_Medida = ReaderInfo["Tipo_Medida"].ToString();
                    Item.Fecha_Expiracion = Convert.ToDateTime(ReaderInfo["Fecha_Expiracion"].ToString());
                    Item.Grupo_Alimenticio = ReaderInfo["Grupo_Alimenticio"].ToString();
                    //Item.ImageIngredient = ReaderInfo["Image"];
                    Item.Cantidad_Maxima = Convert.ToInt16(ReaderInfo["Cantidad_Maxima"].ToString());

                    Item.Cantidad_Maxima = Item.Cantidad_Maxima - Item.Cantidad;
                    if (Item.Cantidad_Maxima > 0)
                    {
                        List_Ingredients.Add(Item);
                    }
                }
            }
            catch (Exception)
            {
                List_Ingredients = null;
            }
            finally
            {
                if (SQLConnection.State == System.Data.ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }
            return (List_Ingredients);
        }

        public static Collection<Ingrediente> ObtenerTodosLosIngredientes()
        {
            Collection<Ingrediente> List_Ingredients = new Collection<Ingrediente>();
            SqlDataReader ReaderInfo = null;

            SqlConnection SQLConnection;
            SQLConnection = new SqlConnection(PathDataBase);

            SqlCommand SQLCommand = SQLConnection.CreateCommand();
            SQLCommand.CommandText = "SELECT * FROM [Ingredient]";

            try
            {
                SQLConnection.Open();
                ReaderInfo = SQLCommand.ExecuteReader();

                while (ReaderInfo.Read())
                {
                    Ingrediente Item = new Ingrediente();

                    Item.Codigo = Convert.ToInt16(ReaderInfo["Codigo"].ToString());
                    Item.Nombre_Ingrediente = ReaderInfo["Nombre_Ingrediente"].ToString();
                    Item.Cantidad = Convert.ToInt16(ReaderInfo["Cantidad"].ToString());
                    Item.Tipo_Medida = ReaderInfo["Tipo_Medida"].ToString();
                    Item.Fecha_Expiracion = Convert.ToDateTime(ReaderInfo["Fecha_Expiracion"].ToString());
                    Item.Grupo_Alimenticio = ReaderInfo["Grupo_Alimenticio"].ToString();
                    //Item.ImageIngredient = ReaderInfo["Image"];
                    Item.Cantidad_Maxima = Convert.ToInt16(ReaderInfo["Cantidad_Maxima"].ToString());

                    List_Ingredients.Add(Item);
                }
            }
            catch (Exception)
            {
                List_Ingredients = null;
            }
            finally
            {
                if (SQLConnection.State == System.Data.ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }

            return (List_Ingredients);
        }

        public static Ingrediente ObtenerUnIngredientePorNombre(string nameIngredient)
        {
            Ingrediente Item = null;

            if (!String.IsNullOrEmpty(nameIngredient))
            {
                SqlDataReader ReaderInfo = null;

                SqlConnection SQLConnection;
                SQLConnection = new SqlConnection(PathDataBase);

                SqlCommand SQLCommand = SQLConnection.CreateCommand();
                SQLCommand.CommandText = "SELECT * FROM [Ingredient] WHERE Nombre_Ingrediente=@Variable_Ingredient_Name";
                SQLCommand.Parameters.AddWithValue("Variable_Ingredient_Name", nameIngredient);

                try
                {
                    SQLConnection.Open();
                    ReaderInfo = SQLCommand.ExecuteReader();

                    while (ReaderInfo.Read())
                    {
                        Item = new Ingrediente();

                        Item.Codigo = Convert.ToInt16(ReaderInfo["Codigo"].ToString());
                        Item.Nombre_Ingrediente = ReaderInfo["Nombre_Ingrediente"].ToString();
                        Item.Cantidad = Convert.ToInt16(ReaderInfo["Cantidad"].ToString());
                        Item.Tipo_Medida = ReaderInfo["Tipo_Medida"].ToString();
                        Item.Fecha_Expiracion = Convert.ToDateTime(ReaderInfo["Fecha_Expiracion"].ToString());
                        Item.Grupo_Alimenticio = ReaderInfo["Grupo_Alimenticio"].ToString();
                        //Item.ImageIngredient = ReaderInfo["Image"];
                        Item.Cantidad_Maxima = Convert.ToInt16(ReaderInfo["Cantidad_Maxima"].ToString());
                    }
                }
                catch (Exception)
                {
                    Item = null;
                }
                finally
                {
                    if (SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        SQLConnection.Close();
                    }
                }
            }

            return (Item);
        }

        public static Collection<Ingrediente> ObtenerIngredientesPorNombre(string nombreIngrediente)
        {
            Collection<Ingrediente> List_Ingredients = new Collection<Ingrediente>();
            SqlDataReader ReaderInfo = null;

            if (!String.IsNullOrEmpty(nombreIngrediente))
            {                
                SqlConnection SQLConnection;
                SQLConnection = new SqlConnection(PathDataBase);

                SqlCommand SQLCommand = SQLConnection.CreateCommand();
                SQLCommand.CommandText = "SELECT * FROM [Ingredient] WHERE Nombre_Ingrediente LIKE @Variable_Ingredient_Name";
                SQLCommand.Parameters.AddWithValue("Variable_Ingredient_Name", nombreIngrediente + "%");

                try
                {
                    SQLConnection.Open();
                    ReaderInfo = SQLCommand.ExecuteReader();

                    while (ReaderInfo.Read())
                    {
                        Ingrediente Item = new Ingrediente();

                        Item.Codigo = Convert.ToInt16(ReaderInfo["Codigo"].ToString());
                        Item.Nombre_Ingrediente = ReaderInfo["Nombre_Ingrediente"].ToString();
                        Item.Cantidad = Convert.ToInt16(ReaderInfo["Cantidad"].ToString());
                        Item.Tipo_Medida = ReaderInfo["Tipo_Medida"].ToString();
                        Item.Fecha_Expiracion = Convert.ToDateTime(ReaderInfo["Fecha_Expiracion"].ToString());
                        Item.Grupo_Alimenticio = ReaderInfo["Grupo_Alimenticio"].ToString();
                        //Item.ImageIngredient = ReaderInfo["Image"];
                        Item.Cantidad_Maxima = Convert.ToInt16(ReaderInfo["Cantidad_Maxima"].ToString());

                        List_Ingredients.Add(Item);
                    }
                }
                catch (Exception)
                {
                    List_Ingredients = null;
                }
                finally
                {
                    if (SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        SQLConnection.Close();
                    }
                }
            }

            return (List_Ingredients);
        }

        public static Ingrediente ObtenerUnIngredientePorCodigo(string code)
        {
            Ingrediente Item = null;

            if (!String.IsNullOrEmpty(code))
            {
                SqlDataReader ReaderInfo = null;

                SqlConnection SQLConnection;
                SQLConnection = new SqlConnection(PathDataBase);

                SqlCommand SQLCommand = SQLConnection.CreateCommand();
                SQLCommand.CommandText = "SELECT * FROM [Ingredient] WHERE Codigo=@Variable_Code";
                SQLCommand.Parameters.AddWithValue("Variable_Code", code);

                try
                {
                    SQLConnection.Open();
                    ReaderInfo = SQLCommand.ExecuteReader();

                    while (ReaderInfo.Read())
                    {
                        Item = new Ingrediente();

                        Item.Codigo = Convert.ToInt16(ReaderInfo["Codigo"].ToString());
                        Item.Nombre_Ingrediente = ReaderInfo["Nombre_Ingrediente"].ToString();
                        Item.Cantidad = Convert.ToInt16(ReaderInfo["Cantidad"].ToString());
                        Item.Tipo_Medida = ReaderInfo["Tipo_Medida"].ToString();
                        Item.Fecha_Expiracion = Convert.ToDateTime(ReaderInfo["Fecha_Expiracion"].ToString());
                        Item.Grupo_Alimenticio = ReaderInfo["Grupo_Alimenticio"].ToString();
                        //Item.ImageIngredient = ReaderInfo["Image"];
                        Item.Cantidad_Maxima = Convert.ToInt16(ReaderInfo["Cantidad_Maxima"].ToString());
                    }
                }
                catch (Exception)
                {
                    Item = null;
                }
                finally
                {
                    if (SQLConnection.State == System.Data.ConnectionState.Open)
                    {
                        SQLConnection.Close();
                    }
                }
            }

            return (Item);
        }

        public static string InsertarIngrediente(Ingrediente ingrediente)
        {
            string Message = "Agregado correctamente";

            SqlConnection SQLConnection;
            SQLConnection = new SqlConnection(PathDataBase);

            SqlCommand SQLCommand = SQLConnection.CreateCommand();
            SQLCommand.CommandText = "INSERT INTO [Ingredient] (Nombre_Ingrediente, Cantidad, Tipo_Medida, Grupo_Alimenticio, Fecha_Expiracion, Cantidad_Maxima) VALUES(@Variable_Ingredient_Name, @Variable_Quantity, @Variable_Measured_Type, @Variable_Food_Group, @Variable_Life_Time, @Variable_Cantidad_Maxima)";
                        
            SQLCommand.Parameters.AddWithValue("Variable_Ingredient_Name", ingrediente.Nombre_Ingrediente);
            SQLCommand.Parameters.AddWithValue("Variable_Quantity", ingrediente.Cantidad);
            SQLCommand.Parameters.AddWithValue("Variable_Measured_Type", ingrediente.Tipo_Medida);            
            SQLCommand.Parameters.AddWithValue("Variable_Food_Group", ingrediente.Grupo_Alimenticio);
            SQLCommand.Parameters.AddWithValue("Variable_Life_Time", ingrediente.Fecha_Expiracion);
            SQLCommand.Parameters.AddWithValue("Variable_Cantidad_Maxima", ingrediente.Cantidad_Maxima);
            //SQLCommand.Parameters.AddWithValue("Variable_Image", ingredient.ImageIngredient);
            //SQLCommand.Parameters.AddWithValue("Variable_Image", null);

            try
            {
                SQLConnection.Open();

                int Result = SQLCommand.ExecuteNonQuery();

                if (Result <= 0)
                {
                    Message = "Error al insertar.";
                }
            }
            catch (Exception exception)
            {
                Message = exception.Message;
            }
            finally
            {
                if (SQLConnection.State == System.Data.ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }

            return (Message);
        }


        public static string ModificarIngrediente(int codigo,Ingrediente ingrediente)
        {
            string Message = "Modificacion correcta";

            SqlConnection SQLConnection;
            SQLConnection = new SqlConnection(PathDataBase);

            SqlCommand SQLCommand = SQLConnection.CreateCommand();
            //SQLCommand.CommandText = "INSERT INTO [Ingredient] (Nombre_Ingrediente, Cantidad, Tipo_Medida, Grupo_Alimenticio, Fecha_Expiracion, Cantidad_Maxima) VALUES(@Variable_Ingredient_Name, @Variable_Quantity, @Variable_Measured_Type, @Variable_Food_Group, @Variable_Life_Time, @Variable_Cantidad_Maxima)";
            SQLCommand.CommandText = "UPDATE [Ingredient] SET Nombre_Ingrediente = @Variable_Ingredient_Name , Fecha_Expiracion = @Variable_Life_Time, Cantidad = @Variable_Quantity , Cantidad_Maxima = @Variable_Cantidad_Maxima ,  Tipo_Medida = @Variable_Measured_Type , Grupo_Alimenticio = @Variable_Food_Group WHERE Codigo = @Codigo";
            SQLCommand.Parameters.AddWithValue("Variable_Ingredient_Name", ingrediente.Nombre_Ingrediente);
            SQLCommand.Parameters.AddWithValue("Variable_Quantity", ingrediente.Cantidad);
            SQLCommand.Parameters.AddWithValue("Variable_Measured_Type", ingrediente.Tipo_Medida);
            SQLCommand.Parameters.AddWithValue("Variable_Food_Group", ingrediente.Grupo_Alimenticio);
            SQLCommand.Parameters.AddWithValue("Variable_Life_Time", ingrediente.Fecha_Expiracion);
            SQLCommand.Parameters.AddWithValue("Variable_Cantidad_Maxima", ingrediente.Cantidad_Maxima);
            SQLCommand.Parameters.AddWithValue("Codigo", codigo);            

            try
            {
                SQLConnection.Open();

                int Result = SQLCommand.ExecuteNonQuery();

                if (Result <= 0)
                {
                    Message = "Error al modificar.";
                }
            }
            catch (Exception exception)
            {
                Message = exception.Message;
            }
            finally
            {
                if (SQLConnection.State == System.Data.ConnectionState.Open)
                {
                    SQLConnection.Close();
                }
            }

            return (Message);
        }


    }
}
